﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.Profile;
using System.ComponentModel.DataAnnotations;
using System.ServiceModel.DomainServices.Hosting;
using System.ServiceModel.DomainServices.Server;
using System.Xml.Serialization;
using System.Runtime.Serialization;
using MyBet.Web.DAL;
using System.Data.SqlClient;

namespace MyBet.Web
{

    [DataContractAttribute(IsReference = true)]
    [Serializable()]
    public class UserObject
    {
        /// <summary>
        /// List of roles
        /// </summary>
        public const string AdministratorRole = "Administrator";
        public const string BetManagerRole = "BetManager";
        public const string PlayerRole = "Player";
        public const string RegisteredUsersRole = "Registered Users";

        private string _userName;
        private Guid _userId;
        private string _email;
        private int _currentContestChampionshipId;
        private List<int> _championshipList;
        private int _currentContestSeasonId;
        private int _currentContestId;
        private int? _rank;
        private int _customContestId;
        private int? _averageScore;
        private int? _maxScore;
        private int? _minScore;
        private int? _betCount;
        private int? _maximumBetCount;
        private Int64? _totalScore;
        private DateTime? _creationDate;
        private string _passwordQuestion;
        private int? _bestOfEventCount;

        [Key()]
        [DataMemberAttribute()]
        public string UserName
        {
            get { return _userName; }
            set { _userName = value; }
        }

        [DataMemberAttribute()]
        public Guid UserId
        {
            get { return _userId; }
            set { _userId = value; }
        }

        [DataMemberAttribute()]
        public string Email
        {
            get { return _email; }
            set { _email = value; }
        }

        [DataMemberAttribute()]
        public int? Rank
        {
            get { return _rank; }
            set { _rank = value; }
        }

        [DataMemberAttribute()]
        public int CurrentContestChampionshipId
        {
            get { return _currentContestChampionshipId; }
            set { _currentContestChampionshipId = value; }
        }

        [DataMemberAttribute()]
        public int CurrentContestSeasonId
        {
            get { return _currentContestSeasonId; }
            set { _currentContestSeasonId = value; }
        }

        [DataMemberAttribute()]
        public List<int> ChampionshipList
        {
            get { return _championshipList; }
            set { _championshipList = value; }
        }

        [DataMemberAttribute()]
        public int CurrentContestId
        {
            get { return _currentContestId; }
            set { _currentContestId = value; }
        }

        [DataMemberAttribute()]
        public int CustomContestId
        {
            get { return _customContestId; }
            set { _customContestId = value; }
        }

        [DataMemberAttribute()]
        public int? AverageScore
        {
            get { return _averageScore; }
            set { _averageScore = value; }
        }

        [DataMemberAttribute()]
        public int? MaxScore
        {
            get { return _maxScore; }
            set { _maxScore = value; }
        }

        [DataMemberAttribute()]
        public int? MinScore
        {
            get { return _minScore; }
            set { _minScore = value; }
        }

        [DataMemberAttribute()]
        public int? BestOfEventCount
        {
            get { return _bestOfEventCount; }
            set { _bestOfEventCount = value; }
        }
        

        [DataMemberAttribute()]
        public int? BetCount
        {
            get { return _betCount; }
            set { _betCount = value; }
        }

        [DataMemberAttribute()]
        public int? MaximumBetCount
        {
            get { return _maximumBetCount; }
            set { _maximumBetCount = value; }
        }

        [DataMemberAttribute()]
        public Int64? TotalScore
        {
            get { return _totalScore; }
            set { _totalScore = value; }
        }

        [DataMemberAttribute()]
        public DateTime? CreationDate
        {
            get { return _creationDate; }
            set { _creationDate = value; }
        }

        [DataMemberAttribute()]
        public string PasswordQuestion
        {
            get { return _passwordQuestion; }
            set { _passwordQuestion = value; }
        }

        /// <summary>
        /// Get User object from login and email
        ///     - id
        ///     - login / email
        ///     - password question
        /// </summary>
        /// <param name="dbContext">Database connection</param>
        /// <param name="login">login</param>
        /// <param name="email">email</param>
        /// <returns></returns>
        public static UserObject GetUserFromLoginAndEmail(MyBetDBEntities dbContext, string login, string email)
        {
            UserObject returnUser = null;
            aspnet_Membership user = (from aspUser in dbContext.aspnet_Membership
                          where ((aspUser.aspnet_Users.LoweredUserName == login.ToLower()) && (aspUser.aspnet_Applications.LoweredApplicationName == "mybet")
                          && aspUser.Email.ToLower() == email.ToLower())
                          select aspUser).SingleOrDefault();

            if (user != null)
            {
                returnUser = new UserObject();
                returnUser.UserName = login;
                returnUser.Email = email;
                returnUser.PasswordQuestion = user.PasswordQuestion;
                returnUser.UserId = user.UserId;
            }

            return returnUser;
        }


        public static UserObject CopyFrom(MyBetDBEntities dbContext, MembershipUser u)
        {
            UserObject user = new UserObject();
            user.UserId = Guid.Empty;
            user.UserName = u.UserName.Trim();
            user.Email = u.Email;
            user.Rank = 0;

            // Get Guid
            if (user.UserId == Guid.Empty)
            {
                var query2 = (from aspUser in dbContext.aspnet_Users
                              where ((aspUser.UserName == u.UserName) && (aspUser.aspnet_Applications.LoweredApplicationName =="mybet"))
                              select aspUser);
                foreach (var aspnet_user in query2)
                {
                    user.UserId = aspnet_user.UserId;
                }
            }

            // Get user information
            object[] parameters1 = 
            {
                new SqlParameter { ParameterName = "p0", Value = user.UserId },
            };


            IEnumerable<UserObject> results = dbContext.ExecuteStoreQuery<UserObject>
                (@"SELECT cc.CustomContestId, c.ChampionshipId as CurrentContestChampionshipId, c.ContestId as CurrentContestId, 
                c.SeasonId as CurrentContestSeasonId, rnk.CurrentRank as Rank, rnk.EventCount as BetCount, 
                rnk.TotalScore, memberShip.CreateDate as CreationDate   
                FROM dbo.Contests as c, dbo.CustomContestLinks ccl, dbo.CustomContest cc, dbo.Subscriptions s
                LEFT OUTER JOIN dbo.Ranks rnk ON  rnk.SubscriptionId = s.SubscriptionId
                LEFT JOIN dbo.aspnet_Membership memberShip ON memberShip.UserId=s.UserId
                WHERE s.CustomContestId = cc.CustomContestId 
                AND cc.CustomContestId = ccl.CustomContestId  
                AND ccl.ContestId = c.ContestId
                AND s.UserId=@p0", parameters1);

            foreach (UserObject sqlUser in results)
            {
                user.CurrentContestChampionshipId = sqlUser.CurrentContestChampionshipId;
                user.CurrentContestId = sqlUser.CurrentContestId;
                user.CurrentContestSeasonId = sqlUser.CurrentContestSeasonId;
                user.ChampionshipList = new List<int>();
                user.ChampionshipList.Add(user.CurrentContestChampionshipId);
                user.Rank = sqlUser.Rank;
                user.CustomContestId = sqlUser.CustomContestId;
                user.BetCount = sqlUser.BetCount;
                user.TotalScore = sqlUser.TotalScore;
                user.CreationDate = sqlUser.CreationDate;
            }

            // Get user stats
            object[] parameters2 = 
            {
                new SqlParameter { ParameterName = "p0", Value = user.UserId },
            };
            IEnumerable<UserObject> statsResult = dbContext.ExecuteStoreQuery<UserObject>
                (@"SELECT AVG(EventScores.Score) as averageScore, MAX(EventScores.Score) as maxScore, MIN(EventScores.Score) as minScore, SUM(EventScores.IsBestScore) as BestOfEventCount
                FROM EventScores
                WHERE EventScores.UserId = @p0", parameters2);
            foreach (UserObject sqlStats in statsResult)
            {
                user.AverageScore = sqlStats.AverageScore;
                user.MinScore = sqlStats.MinScore;
                user.MaxScore = sqlStats.MaxScore;
                user.BestOfEventCount = sqlStats.BestOfEventCount;
            }


            object[] parameters3 = 
            {
                new SqlParameter { ParameterName = "p0", Value = user.UserId },
            };
            IEnumerable<UserObject> maximumBetCountResult = dbContext.ExecuteStoreQuery<UserObject>
                (@"select count(distinct evt.EventId) MaximumBetCount from UserBets ub
                   left join Subscriptions s on s.UserId=ub.UserId
                   left join CustomContestLinks ccl on ccl.CustomContestId = s.CustomContestId
                   left join Contests c on c.ContestId=ccl.ContestId
                   left join Events evt on evt.ContestId = c.ContestId
                   where ub.UserId=@p0 " +
                   "and evt.CloseDate < GETUTCDATE() "+
                   "and c.IsClosed=0", parameters3);
            foreach (UserObject sqlStats in maximumBetCountResult)
            {
                user.MaximumBetCount = sqlStats.MaximumBetCount;
            }

            return user;
        }

        public static UserObject LoadUser(string userName)
        {
            using (MyBetDBEntities dbContext = new MyBetDBEntities())
            {
                MembershipUser u = Membership.GetUser(userName);
                UserObject user = UserObject.CopyFrom(dbContext, u);
                return user;
            }
        }

    }
}